import pymysql
config = {
    'host': '47.98.103.205',
    'port': 3306,
    'user': 'shuah',
    'password': 'Jwlxz723230!',
    'db': 'amazonbest',
    'charset': 'utf8',
}

sqlStr = "INSERT INTO best0711 (country,asin,rankingtype,ranking,star,reviews,moneytype,price,url,name,img ) values (%s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s)"
# sqlStr = "INSERT INTO best (asin,url,name,img,star,reviews,price,ranking ) values (%s, %s, %s, %s, %s, %s, %s, %s)"
sql = '''CREATE TABLE amazon(id INT(13) AUTO_INCREMENT PRIMARY KEY,
country VARCHAR(255),
asin VARCHAR(255),
rankingtype VARCHAR(255),
ranking VARCHAR(255),
star VARCHAR(255), reviews VARCHAR(255),
moneytype VARCHAR(255), price VARCHAR(255),
url VARCHAR(255), 
name VARCHAR(255),
img VARCHAR(255), 
createTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
)'''

# https://www.jianshu.com/p/5b767c6259a6
# https://www.jianshu.com/p/774dae015889
class MysqlUtils(object):     # mysql_conn  主要的功能是，将谅解数据库的操作变成只连接一次
    # 魔术方法, 初始化, 构建函数
    def __init__(self, properties):         # 初始化
        # 创建连接    db相当于mysql的连接
        properties['cursorclass'] = pymysql.cursors.DictCursor
        self.db = pymysql.connect(**properties)
        # 创建游标
        self.cursor = self.db.cursor()

    # 创建表
    def createTable(self, sql):
        conn = self.connectDatabase()
        cur = conn.cursor()
        cur.execute(sql)

    # 执行insert 操作
    def insert(self, sql, params):
        print('insert')
        # 更新,删除或插入操作
        try:
            self.cursor.execute(sql, params)
            self.db.commit()
        except:
            self.db.rollback()

    # 执行modify(修改)相关操作
    def update(self, sql):
        #sql 示例
        # table_name = 'best'
        # d = {'name': 'kora', 'img': 990}  # 更新的字段及对应值
        # t = ('id', 13)  # 更新所需条件
        #
        # sqlStr = 'UPDATE %s SET ' % table_name + ','.join(
        #     ['%s=%r' % (k, d[k]) for k in d]) + ',updateTime = NOW() WHERE %s=%r;' % (t[0], t[1])

        # 更新,删除或插入操作
        try:
            self.cursor.execute(sql)
            self.db.commit()
        except:
            self.db.rollback()

    # 查找操作
    def query(self, sql):
        try:
            self.cursor.execute(sql)
            return self.cursor._rows
            # return self.cursor.fetchmany()
        except:
            self.db.rollback()


    # 魔术方法, 析构化, 析构函数
    def close(self):        #  结束
        #关闭指针对象
        self.cursor.close()
        # 关闭连接对象
        self.db.close()



if __name__ == '__main__':
    mysql = MysqlUtils(config)
    try:
        query = mysql.exe_query("select * from book_detail")
        print(query.fetchall())
    except Exception as e:
        print(888)
    finally:
        mysql.conn_close()
